package cn.com.yusys.yusp.admin.service;

import cn.com.yusys.yusp.admin.Utils.Clob2StringUtils;
import cn.com.yusys.yusp.admin.Utils.RegionUtil;
import cn.com.yusys.yusp.admin.repository.mapper.WcrmGridCustRefMapper;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.BatchResult;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionTemplate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StopWatch;

import java.io.IOException;
import java.sql.Clob;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;


/**
 * @author zoie
 */
@Service
public class WcrmGridCustRefService {
    private static long minRownum = 0;
    private static long maxRownum = 30000;
    private static long range = 30000;
    private Logger logger = LoggerFactory.getLogger(WcrmGridCustRefService.class);
    @Autowired
    private WcrmGridCustRefMapper wcrmGridCustRefMapper;
    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;


    /**
     * 获取WCRM_GRID_CUST_REF客户和网格关系表增量
     *
     * @return
     */
    @Transactional(readOnly = true)
    public List<Map<String, Object>> getGridCusRef() {
        List<Map<String, Object>> list = wcrmGridCustRefMapper.getGridCusRef();
        return list;
    }

    /**
     * 获取WCRM_GRID_MERCHANT_REF增量
     *
     * @return
     */
    @Transactional(readOnly = true)
    public List<Map<String, Object>> getGridMerchantRef() {
        List<Map<String, Object>> list = wcrmGridCustRefMapper.getGridMerchantRef();
        return list;
    }

    /**
     * 批量更新
     * ExecutorType.BATCH
     *
     * @param list
     * @param flag 1:WCRM_GRID_CUST_REF;2:WCRM_GRID_MERCHANT_REF
     */
    @Transactional(readOnly = false, rollbackFor = {Exception.class, RuntimeException.class})
    public void updateBatchMap(List<Map<String, Object>> list, int flag) {
        StopWatch watch = new StopWatch();
        watch.start();
        // 步骤1 获取线程批量session
        SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
        // 步骤2 获取mapper
        WcrmGridCustRefMapper gridCustRefMapper = session.getMapper(WcrmGridCustRefMapper.class);
        // 注意，事务内不允许做查询操作，否则无法做事务回滚，因为查询操作会执行flushStatements
        try {
            if (flag == 1) {
//                list.forEach(demo -> {
//                    gridCustRefMapper.updateGridCustBatch(demo);
//
//                });
                for (int i = 0; i < list.size(); i++) {
                    gridCustRefMapper.updateGridCustBatch(list.get(i));
                    if (i % 1000 == 0 || i == list.size() - 1) {
                        //手动每1000条提交一次，提交后无法回滚
                        logger.info("手动每1000条提交一次：" + i);
                        session.commit(true);
                        //清理缓存，防止溢出
                        session.clearCache();
                    }
                }
            } else if (flag == 2) {
                for (int i = 0; i < list.size(); i++) {
                    gridCustRefMapper.updateGridMerchantBatch(list.get(i));
                    if (i % 1000 == 0 || i == list.size() - 1) {
                        //手动每1000条提交一次，提交后无法回滚
                        logger.info("手动每1000条提交一次：" + i);
                        session.commit(true);
                        //清理缓存，防止溢出
                        session.clearCache();
                    }
                }
//                list.forEach(demo -> {
//                    gridCustRefMapper.updateGridMerchantBatch(demo);
//                });
            }
            // 步骤3 推送更新
//            session.commit();
//            //刷新执行结果可以进行断言操作
//            session.clearCache();
            watch.stop();
            logger.info("批量更新成功,{}", watch.shortSummary());
        } catch (Exception e) {
            logger.error("逻辑系统初始化失败，错误信息:{}", e.getMessage());
            session.rollback();
            throw e;
        } finally {
            session.close();
        }
    }


    /**
     * 批量更新
     * ExecutorType.BATCH
     * 测试数据
     *
     * @param list
     * @param flag 1:WCRM_ALL_CUST_ADDRESS_NEW_F;2:WCRM_MERCHANT_ADDRESS_NEW;3:WCRM_CUST_ORG_ADDRESS
     */
    @Transactional(readOnly = false, rollbackFor = {Exception.class, RuntimeException.class})
    public void updateBatchMapTest(List<Map<String, Object>> list, int flag) {
        StopWatch watch = new StopWatch();
        watch.start();
        // 步骤1 获取线程批量session
        SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
        // 步骤2 获取mapper
        WcrmGridCustRefMapper gridCustRefMapper = session.getMapper(WcrmGridCustRefMapper.class);
        // 注意，事务内不允许做查询操作，否则无法做事务回滚，因为查询操作会执行flushStatements
        try {
            if (flag == 1) {
//                list.forEach(demo -> {
//                    gridCustRefMapper.insertGridCustNewBak(demo);
//                });
                for (int i = 0; i < list.size(); i++) {
                    gridCustRefMapper.insertGridCustNewBak(list.get(i));
                    if (i % 5000 == 0 || i == list.size() - 1) {
                        //手动每5000条提交一次，提交后无法回滚
                        logger.info("手动每5000条提交一次：" + i);
                        session.commit(true);
                        //清理缓存，防止溢出
                        session.clearCache();
                    }
                }
            } else if (flag == 2) {
//                list.forEach(demo -> {
//                    gridCustRefMapper.updateGridMerchant(demo);
//                });
                for (int i = 0; i < list.size(); i++) {
                    gridCustRefMapper.updateGridMerchant(list.get(i));
                    if (i % 5000 == 0 || i == list.size() - 1) {
                        //手动每5000条提交一次，提交后无法回滚
                        logger.info("手动每5000条提交一次：" + i);
                        session.commit(true);
                        //清理缓存，防止溢出
                        session.clearCache();
                    }
                }
            } else if (flag == 3) {
//                list.forEach(demo -> {
//                    gridCustRefMapper.updateOrgAddress(demo);
//                });
                for (int i = 0; i < list.size(); i++) {
                    gridCustRefMapper.updateOrgAddress(list.get(i));
                    if (i % 5000 == 0 || i == list.size() - 1) {
                        //手动每5000条提交一次，提交后无法回滚
                        logger.info("手动每5000条提交一次：" + i);
                        session.commit(true);
                        //清理缓存，防止溢出
                        session.clearCache();
                    }
                }
            }
            // 步骤3 推送更新
//            session.commit();
//            session.clearCache();
            watch.stop();
            logger.info("批量更新成功,{}", watch.shortSummary());
        } catch (Exception e) {
            logger.error("逻辑系统初始化失败，错误信息:{}", e.getMessage());
            session.rollback();
            throw e;
        } finally {
            session.close();
        }
    }

    @Transactional(readOnly = false, rollbackFor = {Exception.class, RuntimeException.class})
    public void update(Map<String, Object> map) {
        wcrmGridCustRefMapper.update(map);
    }

    /**
     * 获取二级网格的经纬度
     *
     * @return
     */
    @Transactional(readOnly = true)
    public List<Map<String, Object>> getLonLatByLevel2() {
        List<Map<String, Object>> list = wcrmGridCustRefMapper.getLonLatByLevel2();
        return list;
    }


    /**
     * 修改商铺增量
     */
    @Transactional(readOnly = false, rollbackFor = {Exception.class, RuntimeException.class})
    public void updateMerchantGrid() {
        //获取增量修改的商铺数据
        List<Map<String, Object>> merchantList = getGridMerchantRef();
        List<Map<String, Object>> list = new ArrayList<>();
        for (Map<String, Object> merchantMap : merchantList) {
            Map<String, Object> map = new HashMap<>();
            //地址不为空
            if (RegionUtil.isBlankOrEmpty(merchantMap.get("address"))) {
                //地址不为空，地址转换经纬度
                String address = merchantMap.get("address").toString();
                address = address.replaceAll("[^0-9a-zA-Z\u4e00-\u9fa5.，,。？“”]+", "");
                Map lngAndLatMap = RegionUtil.getLngAndLat(address);
                //经纬度转换为空则根据主办机构更新
                if (lngAndLatMap.get("lat") != null && lngAndLatMap.get("lng") != null) {
                    map = updateByAddress(merchantMap, lngAndLatMap);
                    if (!map.isEmpty()) {
                        list.add(map);
                    }
                }
            }
        }
        updateBatchMap(list, 2);
        logger.info("=====>>>>>所有商户修改成功:" + list.size());
    }

    /**
     * 生产增量的地址网格转换修改
     *
     * @return
     */
    @Transactional(readOnly = false, rollbackFor = {Exception.class, RuntimeException.class})
    public void updateAddressGrid() {
        int countByAddress = 0;
        //获取增量修改的客户数据
        List<Map<String, Object>> addressList = getGridCusRef();
        List<Map<String, Object>> list = new ArrayList<>();
        for (Map<String, Object> addressMap : addressList) {
            Map<String, Object> map = new HashMap<>();
            if ("0".equals(addressMap.get("isPrivate"))) {
                //1、地址为空，根据主办机构HOST_BRCH_NO
                if (!RegionUtil.isBlankOrEmpty(addressMap.get("address"))) {
                    map = updateByOrg(addressMap);
                    if (!map.isEmpty()) {
                        list.add(map);
                    }
                } else {
                    //地址不为空，地址转换经纬度
                    String address = addressMap.get("address").toString();
                    address = address.replaceAll("[^0-9a-zA-Z\u4e00-\u9fa5.，,。？“”]+", "");
                    Map lngAndLatMap = RegionUtil.getLngAndLat(address);
                    //经纬度转换为空则根据主办机构更新
                    if (lngAndLatMap.get("lat") == null && lngAndLatMap.get("lng") == null) {
                        map = updateByOrg(addressMap);
                    } else {
                        map = updateByAddress(addressMap, lngAndLatMap);
                        if (map.isEmpty()) {
                            map = updateByOrg(addressMap);
                        }else{
                            countByAddress++;
                        }
                    }
                    if (!map.isEmpty()) {
                        list.add(map);
                    }
                }
            } else if ("1".equals(addressMap.get("isPrivate"))) {
                map = updateByOrg(addressMap);
                if (!map.isEmpty()) {
                    list.add(map);
                }
            }
        }
        logger.info("根据地址转换的数据量："+countByAddress);
        logger.info("所有更新数据量："+list.size());
        updateBatchMap(list, 1);
        logger.info("=====>>>>>所有网格客户修改成功：" + list.size());
    }

    /**
     * 根据主办机构修改增量
     *
     * @param addressMap
     * @return
     */
    public Map<String, Object> updateByOrg(Map<String, Object> addressMap) {
        Map<String, Object> map = new HashMap<>();
        try {
            if (RegionUtil.isBlankOrEmpty(addressMap.get("hostBrchNo"))) {
                String orgId = addressMap.get("hostBrchNo").toString();
                List<Map<String, Object>> orgAddressList = wcrmGridCustRefMapper.getLonLatByOrgId(orgId);
                if (!orgAddressList.isEmpty()) {
                    logger.info("主办机构经纬度不为空！");
                    String longitude = Clob2StringUtils.ClobToString((Clob) orgAddressList.get(0).get("longitude"));
                    String latitude = Clob2StringUtils.ClobToString((Clob) orgAddressList.get(0).get("latitude"));
                    map.put("address", orgAddressList.get(0).get("orgAddress") != null ? orgAddressList.get(0).get("orgAddress").toString() : null);
                    map.put("longitude", StringUtils.isNotBlank(longitude) ? longitude : null);
                    map.put("latitude", StringUtils.isNotBlank(latitude) ? latitude : null);
                    map.put("gridId", orgAddressList.get(0).get("gridId") != null ? orgAddressList.get(0).get("gridId").toString() : null);
                    map.put("id", addressMap.get("id"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return map;
    }

    /**
     * 地址，经纬度不为空，获取网格
     *
     * @param addressMap
     * @return
     */
    public Map<String, Object> updateByAddress(Map<String, Object> addressMap, Map<String, Object> lngAndLatMap) {
        Map<String, Object> map = new HashMap<>();
        try {
            String pointLongitude = lngAndLatMap.get("lng").toString();
            String pointLatitude = lngAndLatMap.get("lat").toString();
            Double pointLon = Double.parseDouble(pointLongitude);
            Double pointLat = Double.parseDouble(pointLatitude);
            List<Map<String, Object>> lonLatList = getLonLatByLevel2();
            for (Map<String, Object> lonLat : lonLatList) {
                String latitude = Clob2StringUtils.ClobToString((Clob) lonLat.get("latitude"));
                String longitude = Clob2StringUtils.ClobToString((Clob) lonLat.get("longitude"));
                List<Double> collectLat = Arrays.stream(latitude.split(",")).map(Double::parseDouble).collect(Collectors.toList());
                List<Double> collectLon = Arrays.stream(longitude.split(",")).map(Double::parseDouble).collect(Collectors.toList());
                Double[] doubleLon = new Double[collectLon.size()];
                Double[] doubleLat = new Double[collectLat.size()];
                for (int i = 0; i < collectLat.size(); i++) {
                    doubleLat[i] = collectLat.get(i);
                    doubleLon[i] = collectLon.get(i);
                }
                boolean inPolygon = RegionUtil.isInPolygon(pointLon, pointLat, doubleLon, doubleLat);
                if (inPolygon) {
                    map.put("id", addressMap.get("id"));
                    map.put("gridId", lonLat.get("id"));
                    map.put("latitude", pointLatitude);
                    map.put("longitude", pointLongitude);
                    break;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return map;
    }

    /**
     * 获取测试数据客户和网格关系
     *
     * @return
     */
    @Transactional(readOnly = true)
    public List<Map<String, Object>> getGridCusNewBak(long minRownum, long maxRownum) {
        List<Map<String, Object>> list = wcrmGridCustRefMapper.getGridCusNewBak(minRownum, maxRownum);
        return list;
    }

    /**
     * 获取测试数据商户
     *
     * @return
     */
    @Transactional(readOnly = true)
    public List<Map<String, Object>> getMerchant() {
        List<Map<String, Object>> list = wcrmGridCustRefMapper.getMerchant();
        return list;
    }

    /**
     * 获取测试客户地址增量
     *
     * @return
     */
    public int selectCount() {
        return wcrmGridCustRefMapper.selectCount();
    }

    /**
     * 修改客户地址测试数据
     */
    public void updateAddressGridNewBak() {
        List<Map<String, Object>> lonLatList = getLonLatByLevel2();
        int total = selectCount();
        int k = 0;
        while (minRownum < total) {
            k++;
            List<Map<String, Object>> addressList = getGridCusNewBak(minRownum, maxRownum);
            List<Map<String, Object>> list = new ArrayList<>();
            for (Map<String, Object> addressMap : addressList) {
                Map<String, Object> map = new HashMap<>();
                try {
                    map.put("longitude", addressMap.get("longitude"));
                    map.put("latitude", addressMap.get("latitude"));
                    map.put("custNo", addressMap.get("custNo"));
                    map.put("gridId", addressMap.get("gridId"));
                    map.put("zbOrgId", addressMap.get("zbOrgId"));
                    if (addressMap.get("longitude") != null && addressMap.get("latitude") != null) {
                        String pointLongitude = addressMap.get("longitude").toString();
                        String pointLatitude = addressMap.get("latitude").toString();
                        Double pointLon = Double.parseDouble(pointLongitude);
                        Double pointLat = Double.parseDouble(pointLatitude);
                        for (Map<String, Object> lonLat : lonLatList) {
                            String latitude = Clob2StringUtils.ClobToString((Clob) lonLat.get("latitude"));
                            String longitude = Clob2StringUtils.ClobToString((Clob) lonLat.get("longitude"));
                            List<Double> collectLat = Arrays.stream(latitude.split(",")).map(Double::parseDouble).collect(Collectors.toList());
                            List<Double> collectLon = Arrays.stream(longitude.split(",")).map(Double::parseDouble).collect(Collectors.toList());
                            Double[] doubleLon = new Double[collectLon.size()];
                            Double[] doubleLat = new Double[collectLat.size()];
                            for (int i = 0; i < collectLat.size(); i++) {
                                doubleLat[i] = collectLat.get(i);
                                doubleLon[i] = collectLon.get(i);
                            }
                            boolean inPolygon = RegionUtil.isInPolygon(pointLon, pointLat, doubleLon, doubleLat);
                            if (inPolygon) {
                                map.put("gridId", lonLat.get("id"));
//                                map.put("custNo", addressMap.get("custNo"));
//                                list.add(map);
                                break;
                            }
                        }
                    }
                    list.add(map);
                } catch (SQLException e) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            logger.info("list的大小：" + list.size());
            updateBatchMapTest(list, 1);
            logger.info("=====>>>>>第" + k + "批" + range + "个网格客户判断成功");
            minRownum = range + minRownum;
            maxRownum = range + maxRownum;
        }
        logger.info("=====>>>>>所有网格客户判断成功");
    }

    /**
     * 修改商铺测试数据
     */
    @Transactional(readOnly = false, rollbackFor = {Exception.class, RuntimeException.class})
    public void updateMerchantTest() {
        List<Map<String, Object>> lonLatList = getLonLatByLevel2();
        //获取增量修改的商铺数据
        List<Map<String, Object>> merchantList = getMerchant();
        List<Map<String, Object>> list = new ArrayList<>();
        for (Map<String, Object> merchantMap : merchantList) {
            Map<String, Object> map = new HashMap<>();
            try {
                if (merchantMap.get("longitude") != null && merchantMap.get("latitude") != null) {
                    String pointLongitude = merchantMap.get("longitude").toString();
                    String pointLatitude = merchantMap.get("latitude").toString();
                    Double pointLon = Double.parseDouble(pointLongitude);
                    Double pointLat = Double.parseDouble(pointLatitude);
                    for (Map<String, Object> lonLat : lonLatList) {
                        String latitude = Clob2StringUtils.ClobToString((Clob) lonLat.get("latitude"));
                        String longitude = Clob2StringUtils.ClobToString((Clob) lonLat.get("longitude"));
                        List<Double> collectLat = Arrays.stream(latitude.split(",")).map(Double::parseDouble).collect(Collectors.toList());
                        List<Double> collectLon = Arrays.stream(longitude.split(",")).map(Double::parseDouble).collect(Collectors.toList());
                        Double[] doubleLon = new Double[collectLon.size()];
                        Double[] doubleLat = new Double[collectLat.size()];
                        for (int i = 0; i < collectLat.size(); i++) {
                            doubleLat[i] = collectLat.get(i);
                            doubleLon[i] = collectLon.get(i);
                        }
                        boolean inPolygon = RegionUtil.isInPolygon(pointLon, pointLat, doubleLon, doubleLat);
                        if (inPolygon) {
                            map.put("gridId", lonLat.get("id"));
                            map.put("merchantId", merchantMap.get("merchantId"));
                            list.add(map);
                            break;
                        }
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        logger.info("list的大小：" + list.size());
        updateBatchMapTest(list, 2);
    }

    /**
     * 修改机构测试数据
     */
    @Transactional(readOnly = false, rollbackFor = {Exception.class, RuntimeException.class})
    public void updateOrgTest() {
        List<Map<String, Object>> lonLatList = getLonLatByLevel2();
        //获取增量修改的商铺数据
        List<Map<String, Object>> orgList = wcrmGridCustRefMapper.getOrgAddress();
        List<Map<String, Object>> list = new ArrayList<>();
        for (Map<String, Object> orgMap : orgList) {
            Map<String, Object> map = new HashMap<>();
            try {
                if (orgMap.get("longitude") != null && orgMap.get("latitude") != null) {
                    String pointLatitude = Clob2StringUtils.ClobToString((Clob) orgMap.get("latitude"));
                    String pointLongitude = Clob2StringUtils.ClobToString((Clob) orgMap.get("longitude"));
//                    String pointLongitude = orgMap.get("longitude").toString();
//                    String pointLatitude = orgMap.get("latitude").toString();
                    Double pointLon = Double.parseDouble(pointLongitude);
                    Double pointLat = Double.parseDouble(pointLatitude);
                    for (Map<String, Object> lonLat : lonLatList) {
                        String latitude = Clob2StringUtils.ClobToString((Clob) lonLat.get("latitude"));
                        String longitude = Clob2StringUtils.ClobToString((Clob) lonLat.get("longitude"));
                        List<Double> collectLat = Arrays.stream(latitude.split(",")).map(Double::parseDouble).collect(Collectors.toList());
                        List<Double> collectLon = Arrays.stream(longitude.split(",")).map(Double::parseDouble).collect(Collectors.toList());
                        Double[] doubleLon = new Double[collectLon.size()];
                        Double[] doubleLat = new Double[collectLat.size()];
                        for (int i = 0; i < collectLat.size(); i++) {
                            doubleLat[i] = collectLat.get(i);
                            doubleLon[i] = collectLon.get(i);
                        }
                        boolean inPolygon = RegionUtil.isInPolygon(pointLon, pointLat, doubleLon, doubleLat);
                        if (inPolygon) {
                            map.put("gridId", lonLat.get("id"));
                            map.put("orgId", orgMap.get("orgId"));
                            list.add(map);
                            break;
                        }
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        logger.info("list的大小：" + list.size());
        updateBatchMapTest(list, 3);
    }

    public Map<String, Object> testTrans(String address) {
//        String address ="江苏省苏州市吴江区松陵镇流虹路408号";
        address = address.replaceAll("[^0-9a-zA-Z\u4e00-\u9fa5.，,。？“”]+", "");
        Map lngAndLatMap = RegionUtil.getLngAndLat(address);
        return lngAndLatMap;
    }
}

